package test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sound.midi.Sequence;

/**
 * 
 * @author shefali
 * In all the functions of this class a connection to the database is achieved via a DriverManager class
 * Once the JDBC drivers are loaded, getConnection method is applied to the Driver Manager class using the
 * Appropriate url and username, password
 * 
 * Prepared statement is used to send SQL statements to the database server since it is more efficient than the
 * Statement object
 * The ResultSet class is used to access the table of data generated by executing the sql queries
 *
 */
public class ConferenceDataAccess {
	/**
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 * @throws SQLException
	 * @throws IOException
	 * Selects the conferences between the 2 dates input by the user typically the start Date and the end Date.
      * Returns  a list containing details about all the selected conferences from the db
     *  like the conferenceName, startDate, endDate and the conferenceId
	 */
	public List<Conference> getConferencesForSelectedDates(
			java.sql.Date startDate, java.sql.Date endDate)
			throws SQLException, IOException {
		Connection conn = null;

		List<Conference> conferenceDetails = new ArrayList<Conference>();
		PreparedStatement pstmt = null;
		ResultSet rset;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");

			String query = "select conferenceId, confName, startDate, endDate from Conference  where startDate >= ? and endDate <= ?";

			pstmt = conn.prepareStatement(query);
			pstmt.setDate(1, startDate);
			pstmt.setDate(2, endDate);

			rset = pstmt.executeQuery();
			while (rset.next()) {
				Conference conf = new Conference();
				conf.setConfId(rset.getInt(1));
				conf.setConfName(rset.getString(2));
				conf.setStartDate(rset.getDate(3));
				conf.setEndDate(rset.getDate(4));
				conferenceDetails.add(conf);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return conferenceDetails;

	}
	/**
	 * 
	 * @return List containing the conferneceName and conference Id of all the conferences
	 * @throws SQLException
	 * @throws IOException
	 * This function has been written to populate the drop down lists on the different jsp pages irrespective of the dates on which
	 *  the conferences were held on
	 */

	public List<Conference> getConferences() throws SQLException, IOException {
		Connection conn = null;

		List<Conference> conferences = new ArrayList<Conference>();
		PreparedStatement pstmt = null;
		ResultSet rset;

		Date d = new Date();

		java.sql.Date sqlStartDate = new java.sql.Date(d.getTime());
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");

			String query = "select confName, conferenceId from Conference  where startDate >= ?";
			pstmt = conn.prepareStatement(query);
			pstmt.setDate(1, sqlStartDate);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				Conference conf = new Conference();

				conf.setConfName(rset.getString(1));
				conf.setConfId(rset.getInt(2));

				conferences.add(conf);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return conferences;

	}
/**
 * 
 * @param confName
 * @param startDate
 * @param endDate
 * @return the conference Id generated using Oracle sequences
 * @throws SQLException
 * @throws IOException
 * 
 * Adds a new conference in the database table Conference. Also adds dummy data which acts as a place holder in the workshop table.
 * This makes place for the time slots which will be later assigned to the workshops.
 * Oracle sequence sequence_ConferenceId is used to generate the conference Id which acts as the foreign key in the workshop table
 */
	public int addNewConference(String confName, java.sql.Date startDate,
			java.sql.Date endDate) throws SQLException, IOException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		ResultSet rset = null;
		int x = 0;
		int id = 0;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String user, pass;
			user = "skakodkar";
			pass = "p73505";
			String start = startDate.toString();
			String end = endDate.toString();

			conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
					+ pass + "@oraserver.cs.luc.edu:1521:cs");

			String query = "Insert into Conference(conferenceId,confname,startDate, endDate) values (sequence_ConferenceId.nextval,?,?,?)";
			String query1 = "Insert into Workshop values('xxx', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '9:30am', '11:00am',?,0)";
			String query2 = "Insert into Workshop values('title2', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '11:15am', '12:30pm',?,0)";
			String query3 = "Insert into Workshop values('title3', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '1:00pm', '2:15pm',?,0)";
			String query4 = "Insert into Workshop values('title4', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '2:30pm', '3:45pm',?,0)";
			String query5 = "Insert into Workshop values('title5', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '4:00pm', '5:15pm',?,0)";

			String query6 = "Insert into Workshop values('title6', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '9:30am', '11:00am',?,0)";
			String query7 = "Insert into Workshop values('title7', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '11:15am', '12:30pm',?,0)";
			String query8 = "Insert into Workshop values('title8', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '1:00pm', '2:15pm',?,0)";
			String query9 = "Insert into Workshop values('title9', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '2:30pm', '3:45pm',?,0)";
			String query10 = "Insert into Workshop values('title10', sequence_ConferenceId.currval, 1, 'xxx', 'xxx', 'xxx', '4:00pm', '5:15pm',?,0)";

			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, confName);
			pstmt.setDate(2, startDate);
			pstmt.setDate(3, endDate);
			x = pstmt.executeUpdate();
			pstmt = conn.prepareStatement(query1);
			pstmt.setString(1,start);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query2);
			pstmt.setString(1, start);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query3);
			pstmt.setString(1, start);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query4);
			pstmt.setString(1, start);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query5);
			pstmt.setString(1, start);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query6);
			pstmt.setString(1, end);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query7);
			pstmt.setString(1, end);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query8);
			pstmt.setString(1, end);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query9);
			pstmt.setString(1, end);
			pstmt.executeUpdate();

			pstmt = conn.prepareStatement(query10);
			pstmt.setString(1, end);
			pstmt.executeUpdate();

			String query11 = "select conferenceId from Conference where confName = ? and startDate = ? and endDate = ?";
			pstmt = conn.prepareStatement(query11);
			pstmt.setString(1, confName);
			pstmt.setDate(2, startDate);
			pstmt.setDate(3, endDate);
			rset = pstmt.executeQuery();
			
			while (rset.next()) {
				Conference conf = new Conference();	
			conf.setConfId(rset.getInt(1));
			id = conf.getConfId();
			
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		return id;

	}

}
